﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using Pantry.Commons;
using Pantry.Entity;
using log4net;

namespace Pantry.DataAccess
{
    public class AddressDao
    {
        private readonly SqlConnection _sqlConnection;
        public AddressDao()
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
        }
        #region Constant

        private const string SqlSearchAddress = @"SELECT Address.AddressId AS Id,Address.Address AS Address, County.Postcode AS Postcode, Town.TownName AS Town, County.CountyName AS County, Country.CountryName AS Country
                                                FROM County INNER JOIN Country ON County.CountryId = Country.CountryId INNER JOIN Town ON County.CountyId = Town.CountyId INNER JOIN
                                                Address ON Town.TownId = Address.TownId";

        #endregion
        #region Menthod
        public  DataSet GetAddressesToDataSet()
        {
            return SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, SqlSearchAddress);
        }

        public DataView ReturnSearchValue(AddressSearch addressSearch)
        {
            var dt = GetAddressesToDataSet().Tables[0];
            try
            {
                var dataView = dt.DefaultView;
                if (addressSearch.Postcode.Length > 0)
                {
                    if (addressSearch.Address.Length > 0 && addressSearch.Town.Length > 0)
                    {
                        dataView.RowFilter = string.Format(CultureInfo.CurrentCulture,
                                                           "Postcode = '" + addressSearch.Postcode + "' AND Address LIKE '%" + addressSearch.Address + "%' AND Town LIKE '%" + addressSearch.Town + "%'");
                    }
                    if (addressSearch.Address.Length <= 0 && addressSearch.Town.Length <= 0)
                    {
                        dataView.RowFilter = string.Format(CultureInfo.CurrentCulture,
                                                           "Postcode = '" + addressSearch.Postcode + "'");
                    }
                    if (addressSearch.Address.Length > 0 && addressSearch.Town.Length <= 0)
                    {

                        dataView.RowFilter = string.Format(CultureInfo.CurrentCulture,
                                                           "Postcode = '" + addressSearch.Postcode +
                                                           "' AND Address LIKE '%" + addressSearch.Address + "%'");

                    }
                    if (addressSearch.Address.Length <= 0 && addressSearch.Town.Length > 0)
                    {
                        dataView.RowFilter = string.Format(CultureInfo.CurrentCulture,
                                                           "Postcode = '" + addressSearch.Postcode + "' AND Town LIKE '%" + addressSearch.Town + "%'");
                    }
                }
                else
                {
                    if (addressSearch.Address.Length > 0 && addressSearch.Town.Length > 0)
                    {
                        dataView.RowFilter = string.Format(CultureInfo.CurrentCulture,
                                                           " Address LIKE '%" + addressSearch.Address + "%' AND Town LIKE '%" +
                                                           addressSearch.Town + "%'");
                    }
                    if (addressSearch.Address.Length > 0 && addressSearch.Town.Length <= 0)
                    {
                        dataView.RowFilter = string.Format(CultureInfo.CurrentCulture,
                                                           " Address LIKE '%" + addressSearch.Address + "%'");
                    }
                    if (addressSearch.Address.Length <= 0 && addressSearch.Town.Length > 0)
                    {

                        dataView.RowFilter = string.Format(CultureInfo.CurrentCulture, " Town LIKE '%" + addressSearch.Town + "%'");
                    }
                    if(addressSearch.Address.Length <= 0 && addressSearch.Town.Length <= 0)
                    {
                        return null;
                    }
                }
                return dataView;
            }
            catch (SqlException exception )
            {
                ExceptionHandler.WriteErrorLog(exception);
                return null;
            }
            
        }
        #endregion
    }
}
